

alter                function dbo.f_msproductgetpriorinfo(
  @moneyid integer,
  @productid integer,
  @subjectid integer,
  @nyear integer,
  @nmonth integer,
  @filid integer
)
returns 
  @temp_table table (
    nbalyear integer ,
    nbalmonth integer ,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2),
    bmqtybala decimal(18,8),
    byqtysumcredit decimal(18,8),
    byqtysumdebit decimal(18,8),
    tmqtysumcredit decimal(18,8),
    tmqtysumdebit decimal(18,8)
  )
as
begin
  declare @nbalyear integer
  declare @nbalmonth integer
  declare @maxyear integer
  declare @maxmonth integer
  declare @newfilid integer
  declare @bmbala decimal(18,2) 
  declare @bysumcredit decimal(18,2)
  declare @bysumdebit decimal(18,2)
  declare @tmsumcredit decimal(18,2)
  declare @tmsumdebit decimal(18,2)
  declare @tmsum decimal(18,2)
  declare @fbmbala decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsum decimal(18,2)
  declare @bmqtybala decimal(18,8)
  declare @byqtysumcredit decimal(18,8)
  declare @byqtysumdebit decimal(18,8)
  declare @tmqtysumcredit decimal(18,8)
  declare @tmqtysumdebit decimal(18,8)
  declare @bodata integer

  declare @sum_table table( 
    nbalyear integer,
    nbalmonth integer,
    bmbala decimal(18,8),
    bysumcredit decimal(18,8),
    bysumdebit decimal(18,8),
    tmsumcredit decimal(18,8),
    tmsumdebit decimal(18,8),
    tmsum decimal(18,8),
    fbmbala decimal(18,8),
    fbysumcredit decimal(18,8),
    fbysumdebit decimal(18,8),
    ftmsumcredit decimal(18,8),
    ftmsumdebit decimal(18,8),
    ftmsum decimal(18,8),
    bmqtybala decimal(18,8),
    byqtysumcredit decimal(18,8),
    byqtysumdebit decimal(18,8),
    tmqtysumcredit decimal(18,8),
    tmqtysumdebit decimal(18,8),
    filid integer
  )

  declare cur_fil cursor local forward_only static read_only
    for select fi_filid from td_filiale where fi_filid = @filid or @filid = -99

  open cur_fil 
  fetch next from cur_fil into @newfilid
  while @@fetch_status = 0 
  begin   
    set @nbalyear       = 0
    set @nbalmonth      = 0
    set @bmbala         = 0.00
    set @bysumcredit    = 0.00
    set @bysumdebit     = 0.00
    set @tmsumcredit    = 0.00
    set @tmsumdebit     = 0.00
    set @tmsum          = 0.00
    set @fbmbala        = 0.00
    set @fbysumcredit   = 0.00
    set @fbysumdebit    = 0.00
    set @ftmsumcredit   = 0.00
    set @ftmsumdebit    = 0.00
    set @ftmsum         = 0.00
    set @bmqtybala      = 0.00
    set @byqtysumcredit = 0.00
    set @byqtysumdebit  = 0.00
    set @tmqtysumcredit = 0.00
    set @tmqtysumdebit  = 0.00

    select @bodata=count(subjectid) from tft_msproduct
    where productid = @productid and subjectid = @subjectid
      and (@moneyid is null or moneyid = @moneyid or @moneyid=-99)
      and (nyear < @nyear or (nyear = @nyear and nmonth < @nmonth))
      and filid = @newfilid and isnull(odata,0)=0

    if(@bodata > 0)
    begin
      select top 1 @nbalyear       = nyear,
                   @nbalmonth      = nmonth,
                   @bmbala         = bmbala,
                   @bysumcredit    = bysumcredit,
                   @bysumdebit     = bysumdebit,
                   @tmsumcredit    = tmsumcredit,
                   @tmsumdebit     = tmsumdebit,
                   @tmsum          = tmsum,
                   @fbmbala        = fbmbala,
                   @fbysumcredit   = fbysumcredit,
                   @fbysumdebit    = fbysumdebit,
                   @ftmsumcredit   = ftmsumcredit,
                   @ftmsumdebit    = ftmsumdebit,
                   @ftmsum         = ftmsum,
                   @bmqtybala      = bmqtybala,
                   @byqtysumcredit = byqtysumcredit,
                   @byqtysumdebit  = byqtysumdebit,
                   @tmqtysumcredit = tmqtysumcredit,
                   @tmqtysumdebit  = tmqtysumdebit
      from tft_msproduct
      where productid = @productid and subjectid = @subjectid
        and (@moneyid is null or moneyid = @moneyid or @moneyid=-99)
        and ((nyear < @nyear) or (nyear = @nyear and nmonth < @nmonth))
        and filid = @newfilid and isnull(odata,0)=0
      order by nyear desc, nmonth desc
    end
    else
    begin
      select top 1 @nbalyear       = nyear,
                   @nbalmonth      = nmonth,
                   @bmbala         = bmbala,
                   @bysumcredit    = bysumcredit,
                   @bysumdebit     = bysumdebit,
                   @tmsumcredit    = tmsumcredit,
                   @tmsumdebit     = tmsumdebit,
                   @tmsum          = tmsum,
                   @fbmbala        = fbmbala,
                   @fbysumcredit   = fbysumcredit,
                   @fbysumdebit    = fbysumdebit,
                   @ftmsumcredit   = ftmsumcredit,
                   @ftmsumdebit    = ftmsumdebit,
                   @ftmsum         = ftmsum,
                   @bmqtybala      = bmqtybala,
                   @byqtysumcredit = byqtysumcredit,
                   @byqtysumdebit  = byqtysumdebit,
                   @tmqtysumcredit = tmqtysumcredit,
                   @tmqtysumdebit  = tmqtysumdebit
      from tft_msproduct
      where productid = @productid and subjectid = @subjectid
        and ((nyear < @nyear) or (nyear = @nyear and nmonth < @nmonth))
        and filid = @newfilid and isnull(odata,0)=1
      order by nyear desc, nmonth desc
    end
 
    set @bmbala       = isnull(@bmbala,0.00)
    set @bysumcredit  = isnull(@bysumcredit,0.00)
    set @bysumdebit     = isnull(@bysumdebit,0.00)
    set @tmsumcredit    = isnull(@tmsumcredit,0.00)
    set @tmsumdebit     = isnull(@tmsumdebit,0.00)
    set @tmsum          = isnull(@tmsum,0.00)
    set @fbmbala        = isnull(@fbmbala,0.00)
    set @fbysumcredit   = isnull(@fbysumcredit,0.00)
    set @fbysumdebit    = isnull(@fbysumdebit,0.00)
    set @ftmsumcredit   = isnull(@ftmsumcredit,0.00)
    set @ftmsumdebit    = isnull(@ftmsumdebit,0.00)
    set @ftmsum         = isnull(@ftmsum,0.00)
    set @bmqtybala      = isnull(@bmqtybala,0.00)
    set @byqtysumcredit = isnull(@byqtysumcredit,0.00)
    set @byqtysumdebit  = isnull(@byqtysumdebit,0.00)
    set @tmqtysumcredit = isnull(@tmqtysumcredit,0.00)
    set @tmqtysumdebit  = isnull(@tmqtysumdebit,0.00)
    
    if (@nbalyear <> @nyear)
    begin
      set @bysumcredit  = 0.00
      set @bysumdebit   = 0.00
      set @fbysumcredit = 0.00
      set @fbysumdebit  = 0.00
    end

    insert into @sum_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                 @bysumdebit, @tmsumcredit, @tmsumdebit, @tmsum,
                                 @fbmbala, @fbysumcredit, @fbysumdebit, @ftmsumcredit,
                                 @ftmsumdebit, @ftmsum, @bmqtybala, @byqtysumcredit,
                                 @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit, @newfilid);
    
    update @sum_table set bysumcredit = 0.00, bysumdebit = 0.00,
                          fbysumcredit = 0.00, fbysumdebit = 0.00 
      where nbalyear <> @nyear and filid = @newfilid
    fetch next from cur_fil into  @newfilid;
  end
  close cur_fil
  deallocate  cur_fil

  select @maxyear  = max(nbalyear)  from @sum_table
  select @maxmonth = max(nbalmonth) from @sum_table where nbalyear = @maxyear

  declare cur_return  cursor local forward_only static read_only
    for select nbalyear, nbalmonth, sum(bmbala), sum(bysumcredit), sum(bysumdebit),
      sum(tmsumcredit), sum(tmsumdebit), sum(tmsum), sum(fbmbala), sum(fbysumcredit),
      sum(fbysumdebit), sum(ftmsumcredit), sum(ftmsumdebit), sum(ftmsum),
      sum(bmqtybala), sum(byqtysumcredit), sum(byqtysumdebit), sum(tmqtysumcredit), sum(tmqtysumdebit)
    from @sum_table group by nbalyear, nbalmonth order by nbalyear desc, nbalmonth desc
  open cur_return 
  fetch next from  cur_return into @nbalyear, @nbalmonth, @bmbala,  
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum,
                                  @bmqtybala, @byqtysumcredit, @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit
  while @@fetch_status = 0 
  begin   
    if (@nbalyear = @maxyear and @nbalmonth = @maxmonth)  
    begin
      insert into @temp_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                 @bysumdebit, @tmsumcredit, @tmsumdebit, @tmsum,
                                 @fbmbala, @fbysumcredit, @fbysumdebit, @ftmsumcredit,
                                 @ftmsumdebit, @ftmsum, @bmqtybala, @byqtysumcredit,
                                 @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit);
    end
    if (@nbalyear <> @maxyear or @nbalmonth <> @maxmonth)
    begin
        update @temp_table set bmbala = bmbala + @bmbala + @tmsumdebit - @tmsumcredit,
                               fbmbala = fbmbala + @fbmbala + @ftmsumdebit - @ftmsumcredit
        where nbalyear = @maxyear and nbalmonth = @maxmonth;
    end
    fetch next from cur_return into @nbalyear, @nbalmonth, @bmbala,
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum,
                                  @bmqtybala, @byqtysumcredit, @byqtysumdebit, @tmqtysumcredit, @tmqtysumdebit
  end
  close cur_return
  deallocate  cur_return
  return
end





